package com.ma.lucenedemo.util;

import com.ma.lucenedemo.dto.Result;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/*
* apache下的poi插件
* */
public class PoiUtil {
    /*
    * 导入一个excel：解析
    * 导出一个excel：封装
    * */


    /*
    * 解析excel除表头以外的每一行数据，并封装为对象（指定格式的String类型）
    * */
    public static List<String> parseExcelForKey(MultipartFile multipartFile) throws IOException, InvalidFormatException {
        InputStream inputStream = multipartFile.getInputStream();

        Workbook excel = TabsExcelUtil.isExcelFile(inputStream);
        //获取excel文件流
//        Workbook workbook = WorkbookFactory.create(inputStream);

//        HSSFWorkbook excel = new HSSFWorkbook(inputStream);
        Sheet sheet1 = excel.getSheetAt(0);
//        HSSFSheet sheet = excel.getSheetAt(0);

        //封装数据
        List<String> list = new ArrayList<>();
//        HSSFRow row = null;
        Row row = null;
        for (int i = 1; i <= sheet1.getLastRowNum(); i++) {
//            row = sheet.getRow(i);
            row = sheet1.getRow(i);
            list.add("carName:"+isTypePlus(row.getCell(0)));
        }
        return list;
    }

    /*
    * 生成excel文档（批量数据导出）
    * list：比对封装的结果
    * */
    public static void fileAllDownload(HttpServletResponse response, List<Result> list) throws IOException {
        //设置响应类型并激活下载弹窗
        response.setContentType("application/octet-stream;charset=UTF-8");
        response.addHeader("Content-Disposition","attachment;filename=result.xls");

        //创建excel文件对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建一页
        HSSFSheet sheet = workbook.createSheet("结果映射表");
        //设置表头
        HSSFRow row = null;
        row = sheet.createRow(0);
        HSSFCell cell = null;
        cell = row.createCell(0);
        cell.setCellValue("a_name");
        cell = row.createCell(1);
        cell.setCellValue("b_name");

        //给文件写入数据
        if (list!=null){
            for (int i = 0; i < list.size(); i++) {
                Result result = list.get(i);
                row = sheet.createRow(i+1);
                cell = row.createCell(0);
                cell.setCellValue(result.getAName());
                cell = row.createCell(1);
                cell.setCellValue(result.getBName());
            }
        }
        //磁盘创建并生成excel文件
        FileOutputStream fileOutputStream = new FileOutputStream("/root/matengfei/demo/result/carResult/result.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        workbook.close();

        //创建输出流（方便写文件到本地）
        OutputStream outputStream = response.getOutputStream();
        //创建输入流（读文件）!!!这种方式下载到本地的文件打开有错误！！！
//        FileInputStream fileInputStream = new FileInputStream("D:\项目代码\crm\CRM\下载位置\\activity.xls");
        //这里表示读取服务端文件
//        byte[] bytes = new byte[1024];
//        int length = 0;
//        while ((length=(fileInputStream.read()))!=-1){
//            outputStream.write(bytes,0,length);
//        }
        workbook.write(outputStream);

//        fileInputStream.close();
        outputStream.flush();
    }



    /*
    * 判断单元格中数据的类型
    * */
    public static String isType(HSSFCell cell){
        //遍历每一行的所有列的值（之前需要判断每一列的“数据类型”）
        if (cell.getCellType()== HSSFCell.CELL_TYPE_STRING){
            return cell.getStringCellValue();
        }
        if (cell.getCellType()== HSSFCell.CELL_TYPE_NUMERIC){
            return cell.getNumericCellValue()+"";
        }
        if (cell.getCellType()==HSSFCell.CELL_TYPE_ERROR){
            return cell.getErrorCellValue()+"";
        }
        if (cell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN){
            return cell.getBooleanCellValue()+"";
        }
        return null;
    }

    public static String isTypePlus(Cell cell){
        //遍历每一行的所有列的值（之前需要判断每一列的“数据类型”）
        if (cell.getCellType()== HSSFCell.CELL_TYPE_STRING){
            return cell.getStringCellValue();
        }
        if (cell.getCellType()== HSSFCell.CELL_TYPE_NUMERIC){
            return cell.getNumericCellValue()+"";
        }
        if (cell.getCellType()==HSSFCell.CELL_TYPE_ERROR){
            return cell.getErrorCellValue()+"";
        }
        if (cell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN){
            return cell.getBooleanCellValue()+"";
        }
        return null;
    }



}
